<?php
class Ranking_model extends MY_Model {
	
    function Ranking_model(){
        parent::MY_Model();
        $this->load->database();
        $this->table_name = "ranking";
        $this->table_fields = array('rank_id','kary_id','rank_periode', 'rank_point','rank_ranking');
    }
    
    function select_by_id($rankID){
    	return $this->get_where(array('rank_id'=>$rankID));
    }
    
    function is_exist($kary_id, $periode){
    	$yearMonth = date("Y-m",strtotime($periode));
    	$res = $this->get_where("kary_id='$kary_id' AND DATE_FORMAT(rank_periode,'%Y-%m')='$yearMonth'");
    	return $res->num_rows()>0;
    }
    
    function getID($kary_id, $periode){
    	$yearMonth = date("Y-m",strtotime($periode));
    	$res = $this->get_where("kary_id='$kary_id' AND DATE_FORMAT(rank_periode,'%Y-%m')='$yearMonth'");
    	if ($res->num_rows()>0){
    		$res = $res->row();
    		return $res->rank_id;
    	}
    	return false;
    }
    
    function getPoint($kary_id, $periode){
    	$yearMonth = date("Y-m",strtotime($periode));
    	$res = $this->get_where("kary_id='$kary_id' AND DATE_FORMAT(rank_periode,'%Y-%m')='$yearMonth'");
    	if ($res->num_rows()>0){
    		$res = $res->row();
    		return $res->rank_point;
    	}
    	return 0;
    }
    
    function updatePoint($kary_id, $periode, $point){
    	if ($this->is_exist($kary_id, $periode)){
    		$rank_id = $this->getID($kary_id, $periode);
    		$this->query("UPDATE ranking SET rank_point=rank_point+($point) WHERE rank_id='$rank_id'");
    	}
    	else{
    		$year=date("Y",strtotime($periode));
    		$month=date("m",strtotime($periode))-1;
    		$lastMonthPoint = $this->getPoint($kary_id, "$year-$month-01");
    		$this->insert(array_combine(
    			array('kary_id','rank_periode', 'rank_point'),
    			array($kary_id,$periode, $lastMonthPoint + $point)
    		));
    	}
    }
    
    function selectByPeriode($periode,$orderby=false){
    	if ($orderby) $this->order_by($orderby);
    	return $res = $this->get_where("DATE_FORMAT(rank_periode,'%Y-%m')=DATE_FORMAT('$periode','%Y-%m')");
    }
    
    function select_ranking($search_key=false, $where=false, $limit=10, $offset=0, $orderby=""){
    	$sqlw = "";
    	if ($search_key){
    		$sqlKey="";
    		if (!is_array($search_key)) $search_key = array($search_key);
    		foreach($search_key as $key){
	    		foreach($this->table_fields as $fields){
	    			$sqlKey .= ($sqlKey? " OR " : "")." $this->table_name.$fields LIKE '%$key%' ";
	    		}
	    		
	    		//"kary_id", "kary_nama", "kary_tempat_lahir", "kary_tgl_lahir", "kary_jenis_kelamin", "kary_golongan_darah", "kary_status_marital", "kary_agama", "kary_email", "kary_tgl_masuk", "kary_tgl_keluar", "kary_sts_id"
    			foreach(array("kary_nama") as $fields){
	    			$sqlKey .= ($sqlKey? " OR " : "")." karyawan.$fields LIKE '%$key%' ";
	    		}
    		}
    		$sqlKey = $sqlKey? " ($sqlKey) " : "";
    		$sqlw .= $sqlKey;
    	}
    	
    	if ($where){
    		$sqlw .= $sqlw? " AND $where" : " $where ";
    	}
    	
    	$sqlw = " WHERE $sqlw";
    	
    	$sqlo ="";
   	 	if ($orderby){
    		$sqlo = "ORDER BY $orderby";
    	}
    	
    	$sql = "
    		SELECT $this->table_name.*, karyawan.* as ranking FROM $this->table_name
    		JOIN karyawan ON karyawan.kary_id = $this->table_name.kary_id
    		$sqlw
    		$sqlo
    		LIMIT $offset,$limit;
    	";
    	return $this->query($sql);
    }
}